import pymysql


class Commondata:

    # 数据库连接信息
    db_config = {"sit": {
        "host": "10.10.220.124",
        "user": "root",
        # "password": "Stc@1019temp",
        "password": "cre@mysql8.0",
        "database": ["cre-sigma-test-s3"],
        "port": 3306,
        "maxconnections": [60],  # 最大连接数
        "mincached": 5,  # 最小空闲连接数
        # "maxcached" :20,  # 最大空闲连接数
        "blocking": True,
    },
        # "true":{
        #            "host": "45.252.53.86",
        #             "user": "ture_readonly",
        #             #"password": "Stc@1019temp",
        #             "password": "True!$Read0Nly",
        #             "database": ["cre"],
        #             "port": 23306,
        #             "maxconnections": [30],  # 最大连接数
        #             "mincached": 5,  # 最小空闲连接数
        #             # "maxcached" :20,  # 最大空闲连接数
        #             "blocking": True,
        #             },
        "hotfix": {
            "host": "10.10.168.23",
            "user": "root",
            # "password": "Stc@1019temp",
            "password": "Stc@1019temp",
            "database": ["cre-sigma-test"],
            "port": 31102,
            "maxconnections": [60],  # 最大连接数
            "mincached": 5,  # 最小空闲连接数
            # "maxcached" :20,  # 最大空闲连接数
            "blocking": True,
        },
        "PRO": {
            "host": "118.31.122.162",
            "user": "userr",
            "password": "!QAZ@WSX3edc",
            "database": ["bnzy"],
            "port": 43306,
            "maxconnections": [60],  # 最大连接数
            "mincached": 5,  # 最小空闲连接数
            # "maxcached" :20,  # 最大空闲连接数
            "blocking": True,
        },
        "test": {
            "host": "www.czyl888.com",
            "user": "root",
            "password": "!QAZ@WSX3edc",
            "database": ["bnzy_baojia"],
            "port": 3306,
            "maxconnections": [60],  # 最大连接数
            "mincached": 5,  # 最小空闲连接数
            # "maxcached" :20,  # 最大空闲连接数
            "blocking": True,
        },
        "UAT": {
            "host": "192.168.193.45",
            "user": "root",
            # "password": "Stc@1019temp",
            "password": "1jian8Shu!",
            "database": ["pre_bol_cre"],
            "port": 3307,
            "maxconnections": [60],  # 最大连接数
            "mincached": 5,  # 最小空闲连接数
            # "maxcached" :20,  # 最大空闲连接数
            "blocking": True,
        },

    }

    db_sit = {
        "host": "10.10.220.124",
        "user": "root",
        # "password": "Stc@1019temp",
        "password": "cre@mysql8.0",
        "database": ["cre-sigma-test-s3"],
        "port": 3306,
        "maxconnections": [60],  # 最大连接数
        "mincached": 5,  # 最小空闲连接数
        # "maxcached" :20,  # 最大空闲连接数
        "blocking": True,
    }

    # 统计总的车辆数
    query_user_validation_code_sql = """SELECT
count(1) num
FROM
	d_car_manage_info i
	JOIN d_driver d ON i.user_id = d.id
WHERE
	 i.`status`=2
ORDER BY
	i.create_time DESC;"""

    # 查看当天审核通过后的车辆
    query_car_list = """
    SELECT
	d.name,
	d.phone,
	i.license_code,
	i.vehicle_length
FROM
	d_car_manage_info i
	JOIN d_driver d ON i.user_id = d.id 
WHERE
	 i.`status`=2
	 and DATE_FORMAT(i.create_time,'%Y-%m-%d')>='@a'
	 and DATE_FORMAT(i.create_time,'%Y-%m-%d')<='@b'
ORDER BY
	i.create_time DESC
    """

    # 统计货主人总数
    query_consignor_sum = """
    SELECT
	count(1) as num
FROM
	d_consignor_org_manage c
	LEFT JOIN d_consignor_user u ON c.user_id = u.id 
WHERE
	c.`status` =2
ORDER BY
	c.create_time DESC
    """

    # 查询当天货主人企业认证通过数
    duery_consignor_list = """
    SELECT
	c.company_name AS companyName,
	c.institution_code AS institutionCode,
	c.real_name AS realName,
	c.update_time AS updateTime,
	u.user_name AS phone 
FROM
	d_consignor_org_manage c
	LEFT JOIN d_consignor_user u ON c.user_id = u.id 
WHERE
	c.`status` =2
	and DATE_FORMAT(c.update_time,'%Y-%m-%d')>='@a'
	and DATE_FORMAT(c.update_time,'%Y-%m-%d')<='@b'
ORDER BY
	c.create_time DESC
    """


    # 线路企业数 -- 作废
    query_logistics_line = """
    SELECT
	aa.updateTime AS updateTime,
	aa.realName AS realName,
	aa.phone AS phone,
	aa.orgName AS orgName,
	aa.orgCode AS orgCode 
FROM
	(
	SELECT
		u.id AS id,
		u.update_time AS updateTime,
		u.user_name AS userName,
		m.real_name AS realName,
		u.phone AS phone,
		u.org_name AS orgName,
		u.org_code AS orgCode,
		u.create_time AS create_time 
	FROM
		d_logistics_user u
		LEFT JOIN d_logistics_org_manage o ON u.id = o.user_id 
		AND o.STATUS = '2'
		LEFT JOIN d_logistics_network_site n ON n.logistics_id = u.id
		LEFT JOIN d_logistics_user_manage m ON u.id = m.user_id 
		AND m.STATUS = '2' 
	WHERE
		u.STATUS = '1' 
		AND m.id IS NOT NULL 
	GROUP BY
		u.id 
	ORDER BY
		u.create_time DESC 
	) aa,
	d_product_line_quotation d 
WHERE
	aa.id = d.logistics_user_id 
	AND d.`status` = 2 
GROUP BY
	aa.updateTime,
	aa.realName,
	aa.phone,
	aa.orgName,
	aa.orgCode
order by aa.updateTime desc
    """

    # 当天新增加企业线路数
    query_logistics_line_today = """
    SELECT
	aa.updateTime AS updateTime,
	aa.realName AS realName,
	aa.phone AS phone,
	aa.orgName AS orgName,
	aa.orgCode AS orgCode 
FROM
	(
	SELECT
		u.id AS id,
		u.update_time AS updateTime,
		u.user_name AS userName,
		m.real_name AS realName,
		u.phone AS phone,
		u.org_name AS orgName,
		u.org_code AS orgCode,
		u.create_time AS create_time 
	FROM
		d_logistics_user u
		LEFT JOIN d_logistics_org_manage o ON u.id = o.user_id 
		AND o.STATUS = '2'
		LEFT JOIN d_logistics_network_site n ON n.logistics_id = u.id
		LEFT JOIN d_logistics_user_manage m ON u.id = m.user_id 
		AND m.STATUS = '2' 
	WHERE
		u.STATUS = '1' 
		AND m.id IS NOT NULL 
	GROUP BY
		u.id 
	ORDER BY
		u.create_time DESC 
	) aa,
	d_product_line_quotation d 
WHERE
	aa.id = d.logistics_user_id 
	AND d.`status` = 2 
	and DATE_FORMAT(aa.updateTime,'%Y-%m-%d')>='@a'
	and DATE_FORMAT(aa.updateTime,'%Y-%m-%d')<='@b'
GROUP BY
	aa.updateTime,
	aa.realName,
	aa.phone,
	aa.orgName,
	aa.orgCode
order by aa.updateTime desc;
    """

    #  当天的运单数-- 作废
    query_waybill_list_today="""
    SELECT
    DATE_FORMAT(w.create_time, '%Y-%m-%d') AS createTime,
    w.update_time AS updateTime,
    (select real_name from d_consignor_user where id = w.create_id) as realname,
	(select phone from d_consignor_user where id = w.create_id) as phone,
    w.STATUS,
    w.order_type AS orderType,
    w.pay_status AS payStatus,
    w.pay_price / 100.0 AS payPrice,
    w.total_fee / 100.0 AS totalFee,
    CASE
        WHEN w.waybill_status = 0 THEN '待接单'
        WHEN w.waybill_status = 1 THEN '待提货'
        WHEN w.waybill_status = 2 THEN '在途'
        WHEN w.waybill_status = 3 THEN '待确认'
        WHEN w.waybill_status = 4 THEN '已完成'
        WHEN w.waybill_status = 5 THEN '待结算'
        WHEN w.waybill_status = 6 THEN '已取消'
        WHEN w.waybill_status = 7 THEN '复磅中'
        WHEN w.waybill_status = 8 THEN '复磅支付'
        WHEN w.waybill_status = 9 THEN '作废'
        ELSE '其他'
    END AS waybillStatus,
    w.CODE as code,
    w.consignor_name AS consignorName,
    w.consignor_phone AS consignorPhone,
    w.consignee_phone AS consigneePhone
FROM
    d_waybill w
    LEFT JOIN d_logistics_order o ON w.first_logistic_order_id = o.id
    LEFT JOIN (
        SELECT
            waybill_id,
            SUM(weight) AS cargoWeight,
            SUM(volume) AS cargoVolume,
            GROUP_CONCAT(DISTINCT cargo_name SEPARATOR ',') AS cargoName,
            SUM(number_cases) AS numberCases
        FROM
            d_waybill_cargo
        WHERE
            STATUS = '1'
        GROUP BY
            waybill_id
    ) wco ON w.id = wco.waybill_id
    LEFT JOIN d_logistics_network_site ns ON ns.id = o.accepting_id
WHERE
    w.STATUS = '1'
		and DATE_FORMAT(w.create_time,'%Y-%m-%d')>='@a'
		and DATE_FORMAT(w.create_time,'%Y-%m-%d')<='@b'
ORDER BY
    w.create_time DESC;
    """

    # 本周 运单，物流， 司机单汇总，总支出和收入
    query_waybill_list_today2 ="""
    SELECT
	w.id waybillId,
	w.CODE waybillCode,
	l.order_code orderCode,
	GROUP_CONCAT( DO.order_code SEPARATOR '/' ) driverOrder,
	w.create_time createTime,
	CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')) cooperationUser,
	CONCAT( COALESCE(u.real_name,''), '/',COALESCE(u.phone,''),'/', COALESCE(u.org_name,'')) consignorUser,
	CONCAT(w.starting_province,w.starting_city,w.starting_district,w.starting_street,w.consignor_adress) startingAddress,
  CONCAT(w.destination_province,w.destination_city,w.destination_district,w.destination_street,w.consignee_adress) destinationAddress,
  GROUP_CONCAT(cargo.cargo_name SEPARATOR '/') cargoName,
  GROUP_CONCAT(cargo.weight/1000 SEPARATOR '/') cargoWeight,
  GROUP_CONCAT(cargo.volume/1000 SEPARATOR '/') cargoVolume,
	u3.org_name logisticsUser,
	GROUP_CONCAT( DO.license_code SEPARATOR '/' ) licenseCode,
	GROUP_CONCAT( DO.driver_name SEPARATOR '/' ) driverName,
	GROUP_CONCAT( DO.driver_phone SEPARATOR '/' ) driverPhone,
	round((w.freight_charge/100.0),2) as 货主报价,
  (SELECT
   round(sum(o.amount/100.0),2) as amount
   FROM
   d_order_info o
   WHERE
   ( user_id = '1' OR receivables_id = '1' ) 
   AND STATUS IN ( '2', '4' ) 
   AND order_type != '1' 
   AND order_type != '2' 
   and o.receivables_id = 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))) as 平台总收入,
	 (SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
    and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code)) ) as 平台总支出,
 
		(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=l.order_code and d.receivables_id=u2.id and d.receivables_user_type='5') as 合伙人佣金支出,
		 CASE
        WHEN w.waybill_status = 0 THEN '待接单'
        WHEN w.waybill_status = 1 THEN '待提货'
        WHEN w.waybill_status = 2 THEN '在途'
        WHEN w.waybill_status = 3 THEN '待确认'
        WHEN w.waybill_status = 4 THEN '已完成'
        WHEN w.waybill_status = 5 THEN '待结算'
        WHEN w.waybill_status = 6 THEN '已取消'
        WHEN w.waybill_status = 7 THEN '复磅中'
        WHEN w.waybill_status = 8 THEN '复磅支付'
        WHEN w.waybill_status = 9 THEN '作废'
        ELSE '其他'
    END AS waybillStatus,
	 CASE
        WHEN l.order_type = 0 THEN '零担'
        WHEN l.order_type = 1 THEN '合作物流'
        WHEN l.order_type = 2 THEN '配载'
        WHEN l.order_type = 3 THEN '转交'
        WHEN l.order_type = 4 THEN '转交落地配'
        WHEN l.order_type = 5 THEN '整车'
        ELSE '其他'
    END AS orderType
		
FROM
	d_logistics_order l
	LEFT JOIN d_waybill w ON l.waybill_id = w.id
	LEFT JOIN ( SELECT logistics_order_id, GROUP_CONCAT( order_code SEPARATOR '/' ) order_code, license_code, driver_name, driver_phone FROM d_driver_order /*WHERE TYPE != 0 AND TYPE != 4*/ GROUP BY logistics_order_id ) DO ON DO.logistics_order_id = l.id
	LEFT JOIN d_consignor_user u ON u.id = w.create_id
	LEFT JOIN d_cooperation_user u2 ON u.invite_code = u2.invite_code
	LEFT JOIN d_user_housekeeper_ref hr ON hr.user_id = u.id
	LEFT JOIN d_logistics_housekeeper h ON hr.housekeeper_id = h.id
	LEFT JOIN (
    SELECT
    lcg.order_id,
    GROUP_CONCAT( cg.cargo_name SEPARATOR '/' ) AS cargo_name,
    GROUP_CONCAT( cg.weight SEPARATOR '/' ) AS weight,
    GROUP_CONCAT( cg.volume SEPARATOR '/' ) AS volume 
    FROM
    d_logistics_order_cargo lcg
    JOIN d_waybill_cargo cg ON cg.id = lcg.waybill_cargo_id 
    GROUP BY
    lcg.order_id 
  ) cargo ON cargo.order_id = l.id
	LEFT JOIN d_logistics_user u3 ON u3.id = l.accepting_id 
WHERE
	1 = 1 
	and w.waybill_status!=9
	and DATE_FORMAT(w.create_time,'%Y-%m-%d')>='@a'
	and DATE_FORMAT(w.create_time,'%Y-%m-%d')<='@b'
	-- and w.code='YD2407021144050007'
GROUP BY
	l.id 
HAVING
	1 = 1 
ORDER BY
	w.create_time DESC
    """

    #  完成订单数
    query_waybill_end_sum = """
    SELECT
   count(1)  as num
FROM
    d_waybill w
    LEFT JOIN d_logistics_order o ON w.first_logistic_order_id = o.id
    LEFT JOIN (
        SELECT
            waybill_id,
            SUM(weight) AS cargoWeight,
            SUM(volume) AS cargoVolume,
            GROUP_CONCAT(DISTINCT cargo_name SEPARATOR ',') AS cargoName,
            SUM(number_cases) AS numberCases
        FROM
            d_waybill_cargo
        WHERE
            STATUS = '1'
        GROUP BY
            waybill_id
    ) wco ON w.id = wco.waybill_id
    LEFT JOIN d_logistics_network_site ns ON ns.id = o.accepting_id
WHERE
    w.STATUS = '1'
		#and DATE_FORMAT(w.create_time,'%Y-%m-%d')='2024-10-28'
		and w.waybill_status = 4
ORDER BY
    w.create_time DESC;
    """

    # 统计分账总收入和总支出
    query_amount = """
    SELECT
	type,
	sum(fee/100.0) as amount
FROM
	(
	SELECT
		o.id,
		o.create_time createTime,
		o.update_time updateTime,
		o.STATUS,
	IF
		( o.receivables_id = 1, 1, 2 ) TYPE,
		o.amount fee,
		o.order_user_type orderType,
		o.order_code orderId,
	IF
		( userName = '平台', receivablesUserName, userName ) userName 
	FROM
		d_order_info o
		JOIN (
		SELECT
			id,
			CONCAT(
				'物流/',
			IFNULL( org_name, real_name )) userName 
		FROM
			d_logistics_user UNION ALL
		SELECT
			id,
			CONCAT( '货主/', real_name ) userName 
		FROM
			d_consignor_user UNION ALL
		SELECT
			id,
			CONCAT( '合伙人/', real_name ) userName 
		FROM
			d_cooperation_user UNION ALL
		SELECT
			id,
			CONCAT( '司机/', NAME ) userName 
		FROM
			d_driver UNION ALL
		SELECT
			'1',
			'平台' receivablesUserName 
		FROM
		DUAL 
		) AS user_info ON o.user_id = user_info.id
		JOIN (
		SELECT
			id,
			CONCAT(
				'物流/',
			IFNULL( org_name, real_name )) receivablesUserName 
		FROM
			d_logistics_user UNION ALL
		SELECT
			id,
			CONCAT( '货主/', real_name ) receivablesUserName 
		FROM
			d_consignor_user UNION ALL
		SELECT
			id,
			CONCAT( '合伙人/', real_name ) receivablesUserName 
		FROM
			d_cooperation_user UNION ALL
		SELECT
			id,
			CONCAT( '司机/', NAME ) receivablesUserName 
		FROM
			d_driver UNION ALL
		SELECT
			'1',
			'平台' receivablesUserName 
		FROM
		DUAL 
		) AS receivables_info ON o.receivables_id = receivables_info.id 
	WHERE
		( user_id = '1' OR receivables_id = '1' ) 
		AND STATUS IN ( '2', '4' ) 
		AND order_type != '1' 
		AND order_type != '2' 
	) t 
WHERE
	1 = 1 
	AND '@a' <= DATE_FORMAT(updateTime,'%Y-%m-%d') 
	AND '@b' >= DATE_FORMAT(updateTime,'%Y-%m-%d')
group by TYPE
    """

    # 统计物流线路
    query_logistics_line_new="""
    SELECT
	(select org_name from d_logistics_user where id = d.logistics_user_id)  公司名称,
	d.forwarding_province 发站省,
	d.forwarding_city 发站市,
	d.forwarding_district 发站区,
	d.forwarding_street 发站街道,
	d.forwarding_address 发站仓库地址,
	d.arrive_province 到达省,
	d.arrive_city 到达市,
	d.arrive_district 到达区,
	d.arrive_street 到达街道,
	d.arrive_address 到达仓库地址,
	d.haulage_time 时效,
	round(d.basic_price / 10000,2) '起步价/元',
	-- d.transport_type 运输类型,
	IF(d.is_including_tax = '0', '不含税', '含税') 是否含税,
	concat(d.service_rate,'%') 服务费率,
	-- p.id id,
	-- p.line_quotation_id lineQuotationId,
	round(p.unit_price / 10000,2) '运费/元',
	CONCAT(p.start_quantity, '-', p.end_quantity) as 重量区间,
	-- p.flat_type flatType,
	IF(p.STATUS = '2', '已通过', '未通过') '审核状态',
	DATE_FORMAT( p.create_time, '%Y-%m-%d %H:%i:%s' ) createTime,
	DATE_FORMAT( p.update_time, '%Y-%m-%d %H:%i:%s' ) updateTime
FROM
	d_product_line_quotation_price p
	LEFT JOIN d_product_line_quotation d ON d.id = p.line_quotation_id 
WHERE
-- 	d.logistics_user_id = 'b23b6a3e1fc24399a68d3b5638dac2ff' 
-- 	and 
d.forwarding_province='@a'
ORDER BY
	p.create_time DESC
    """

    # 统计7月至今订单票情况-- 废弃
    query_waybill_list = """
    SELECT
	w.id waybillId,
	w.CODE waybillCode,
	l.order_code orderCode,
	GROUP_CONCAT( DO.order_code SEPARATOR '/' ) driverOrder,
	w.create_time createTime,
	CONCAT(if(u2.bind_invite_code='' or u2.bind_invite_code is null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),(select CONCAT( COALESCE(real_name,''), '/', COALESCE(phone,'')) from d_cooperation_user where invite_code=u2.bind_invite_code)),'/',(select profit_ratio from d_invite_code_log t where t.invite_code=u2.invite_code and t.consignor_id=u.id)) 一级合伙人,
	CONCAT(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),''),'/',(select profit_ratio from d_invite_code_log t where t.invite_code=u2.bind_invite_code and t.consignor_id=u2.id)) 二级合伙人,
	-- CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')) cooperationUser,
	CONCAT( COALESCE(u.real_name,''), '/',COALESCE(u.phone,''),'/', COALESCE(u.org_name,'')) consignorUser,
	-- u.id,
	u2.id,
  l.order_code,
	CONCAT(w.starting_province,w.starting_city,w.starting_district,w.starting_street,w.consignor_adress) startingAddress,
  CONCAT(w.destination_province,w.destination_city,w.destination_district,w.destination_street,w.consignee_adress) destinationAddress,
  GROUP_CONCAT(cargo.cargo_name SEPARATOR '/') cargoName,
  GROUP_CONCAT(cargo.weight/1000 SEPARATOR '/') cargoWeight,
  GROUP_CONCAT(cargo.volume/1000 SEPARATOR '/') cargoVolume,
	u3.org_name logisticsUser,
	GROUP_CONCAT( DO.license_code SEPARATOR '/' ) licenseCode,
	GROUP_CONCAT( DO.driver_name SEPARATOR '/' ) driverName,
	GROUP_CONCAT( DO.driver_phone SEPARATOR '/' ) driverPhone,
	round((w.freight_charge/100.0),2) as 货主报价,
	round((DO.freight_charge/100.0),2) as 司机报价,
	if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 实际给司机价,
  (SELECT
   round(sum(o.amount/100.0),2) as amount
   FROM
   d_order_info o
   WHERE
   ( user_id = '1' OR receivables_id = '1' ) 
   AND STATUS IN ( '2', '4' ) 
   AND order_type != '1' 
   AND order_type != '2' 
   and o.receivables_id = 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))) as 平台总收入,
	 (SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
    and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code)) ) as 平台总支出,
 
		(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=l.order_code and d.receivables_id=u2.id and d.receivables_user_type='5') as 合伙人佣金支出,
		(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=DO.order_code and d.receivables_id=DO.accepting_id ) as 司机支出,
		 CASE
        WHEN w.waybill_status = 0 THEN '待接单'
        WHEN w.waybill_status = 1 THEN '待提货'
        WHEN w.waybill_status = 2 THEN '在途'
        WHEN w.waybill_status = 3 THEN '待确认'
        WHEN w.waybill_status = 4 THEN '已完成'
        WHEN w.waybill_status = 5 THEN '待结算'
        WHEN w.waybill_status = 6 THEN '已取消'
        WHEN w.waybill_status = 7 THEN '复磅中'
        WHEN w.waybill_status = 8 THEN '复磅支付'
        WHEN w.waybill_status = 9 THEN '作废'
        ELSE '其他'
    END AS waybillStatus,
	 CASE
        WHEN l.order_type = 0 THEN '零担'
        WHEN l.order_type = 1 THEN '合作物流'
        WHEN l.order_type = 2 THEN '配载'
        WHEN l.order_type = 3 THEN '转交'
        WHEN l.order_type = 4 THEN '转交落地配'
        WHEN l.order_type = 5 THEN '整车'
        ELSE '其他'
    END AS orderType,
	CASE
        WHEN DO.arrive_pay_type = 0 THEN '到付-货主付'
        WHEN DO.arrive_pay_type = 1 THEN '到付-收货人付'
        ELSE '现付'
    END AS 支付类型
FROM
	d_logistics_order l
	LEFT JOIN d_waybill w ON l.waybill_id = w.id
	LEFT JOIN ( SELECT logistics_order_id, GROUP_CONCAT( order_code SEPARATOR '/' ) order_code, license_code, driver_name, driver_phone,accepting_id,arrive_pay_type,freight_charge,actual_payment_fee FROM d_driver_order /*WHERE TYPE != 0 AND TYPE != 4*/ GROUP BY logistics_order_id ) DO ON DO.logistics_order_id = l.id
	LEFT JOIN d_consignor_user u ON u.id = w.create_id
	LEFT JOIN d_cooperation_user u2 ON u.invite_code = u2.invite_code
	LEFT JOIN d_user_housekeeper_ref hr ON hr.user_id = u.id
	LEFT JOIN d_logistics_housekeeper h ON hr.housekeeper_id = h.id
	LEFT JOIN (
    SELECT
    lcg.order_id,
    GROUP_CONCAT( cg.cargo_name SEPARATOR '/' ) AS cargo_name,
    GROUP_CONCAT( cg.weight SEPARATOR '/' ) AS weight,
    GROUP_CONCAT( cg.volume SEPARATOR '/' ) AS volume 
    FROM
    d_logistics_order_cargo lcg
    JOIN d_waybill_cargo cg ON cg.id = lcg.waybill_cargo_id 
    GROUP BY
    lcg.order_id 
  ) cargo ON cargo.order_id = l.id
	LEFT JOIN d_logistics_user u3 ON u3.id = l.accepting_id 
WHERE
	1 = 1 
	and w.waybill_status!=9
	and DATE_FORMAT(w.create_time,'%Y-%m-%d')>='2024-07-01'
	-- and DATE_FORMAT(w.create_time,'%Y-%m-%d')<='2024-11-29'
  -- and w.code='YD2411291137120001'
GROUP BY
	l.id 
HAVING
	1 = 1 
ORDER BY
	w.create_time DESC
    """


    ##  最新统计

    query_waybill_list1_20250904 = """
      select * from (
SELECT
   l.id,
		w.create_time YD创建时间,
		l.create_time WL单创建时间,
  	DO.create_time SJ单创建时间,
	CONCAT( COALESCE(u.real_name,''), '/',COALESCE(u.phone,''),'/', COALESCE(u.org_name,'')) 客户名称,
	-- w.id waybillId,
	w.CODE waybillCode,
-- 	w.starting_province as starting_province,
-- 	w.starting_city as starting_city,
-- 	w.starting_district as starting_district,
-- 	w.starting_street as starting_street,
-- 	w.consignor_adress as consignor_adress,
-- 	w.destination_province as destination_province,
-- 	w.destination_city as destination_city,
-- 	w.destination_district as destination_district,
-- 	w.destination_street as destination_street,
-- 	w.consignee_adress as consignee_adress,
    CONCAT_WS('',w.starting_province,w.starting_city,w.starting_district,w.starting_street,w.consignor_adress) startingAddress,
    CONCAT_WS('',w.destination_province,w.destination_city,w.destination_district,w.destination_street,w.consignee_adress) destinationAddress,
    l.cargo_name 货物名称,
	DO.cargo_pack 货物包装,
	cargo.weight/1000 cargoWeight,
	cargo.volume/1000 cargoVolume,
	round((w.freight_charge/100.0),2) as 货主报价,
	(SELECT
   round(sum(o.amount/100.0),2) as amount
   FROM
   d_order_info o
   WHERE
   ( user_id = '1' OR receivables_id = '1' ) 
   AND STATUS IN ( '2', '4' ) 
   AND order_type != '1' 
   AND order_type != '2' 
   and o.receivables_id = 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))) as 平台总收入,
	
	if(l.accepting_id = 'admin','乐享智运', u3.org_name) 品牌物流,
	-- u3.id,
	l.order_code orderCode,
	
	(SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))
	 and o.receivables_id=u3.id) 线路价,
	DO.driver_name driverName ,
	DO.driver_phone driver_phone,
	DO.license_code licenseCode,
	DO.order_code driverOrder,
	concat_ws('',DO.starting_province,DO.starting_city,DO.starting_district,NULLIF(DO.starting_street,''),NULLIF(DO.starting_adress,'')) 司机单发货地,
	concat_ws('',DO.destination_province,DO.destination_city,DO.destination_district,NULLIF(DO.destination_street,''),NULLIF(DO.destination_adress,'')) 司机单卸货地,
	DO.actual_load_time 装货时间,
  -- DO.unload_time 卸货时间,
	COALESCE(a.unloading_time, IF(b.recipient_status IN ('1', '2'), b.update_time, NULL), DO.unload_time) as 卸货时间,
		if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 司机运费,
	CONCAT(if(u2.bind_invite_code='' or u2.bind_invite_code is null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),(select CONCAT( COALESCE(real_name,''), '/', COALESCE(phone,'')) from d_cooperation_user where invite_code=u2.bind_invite_code)),'/',(	if(u2.bind_invite_code='' or u2.bind_invite_code is null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),(select GROUP_CONCAT(CONCAT(profit_ratio, '/', service_ratio) SEPARATOR ', ') from d_invite_code_log t where t.invite_code=u2.bind_invite_code and t.consignor_id=u2.id)))) 一级合伙人,
	CONCAT(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),''),'/',(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),''))) 二级合伙人,
	(select GROUP_CONCAT(round(d.amount/100.0,2) SEPARATOR '/') from d_order_info d where d.order_code=l.order_code /*and d.receivables_id=u2.id*/ and d.receivables_id!='null' and d.receivables_user_type='5') as 合伙人佣金支出,
	 CASE
        WHEN w.waybill_status = 0 THEN '待接单'
        WHEN w.waybill_status = 1 THEN '待提货'
        WHEN w.waybill_status = 2 THEN '在途'
        WHEN w.waybill_status = 3 THEN '待确认'
        WHEN w.waybill_status = 4 THEN '已完成'
        WHEN w.waybill_status = 5 THEN '待结算'
        WHEN w.waybill_status = 6 THEN '已取消'
        WHEN w.waybill_status = 7 THEN '复磅中'
        WHEN w.waybill_status = 8 THEN '复磅支付'
        WHEN w.waybill_status = 9 THEN '作废'
        ELSE '其他'
    END AS waybillStatus,
		 CASE
        WHEN l.del_status = 0 THEN '待收货'
        WHEN l.del_status = 1 THEN '待处理（提货完成）'
        WHEN l.del_status = 2 THEN '转交中'
        WHEN l.del_status = 3 THEN '已转交'
        WHEN l.del_status = 4 THEN '发货中（发布司机订单）'
        WHEN l.del_status = 5 THEN '已发货（司机装货）'
        WHEN l.del_status = 6 THEN '配载中'
        WHEN l.del_status = 7 THEN '配载完成'
        WHEN l.del_status = 8 THEN '已完成'
        WHEN l.del_status = 9 THEN '待结算'
				WHEN l.del_status = 10 THEN '待复磅'
				WHEN l.del_status = 11 THEN '已复磅'
				WHEN l.del_status = 12 THEN '作废'
				WHEN l.del_status = 13 THEN '复磅支付'
				WHEN l.del_status = 14 THEN '处理中'
				WHEN l.del_status = 15 THEN '到货待确认'
				WHEN l.del_status = 16 THEN '复磅待确认'
				WHEN l.del_status = 17 THEN '待付服务费'
        ELSE '其他'
    END AS logisticStatus,
			 CASE
        WHEN l.order_type = 0 THEN '零担'
        WHEN l.order_type = 1 THEN '合作物流'
        WHEN l.order_type = 2 THEN '配载'
        WHEN l.order_type = 3 THEN '转交'
        WHEN l.order_type = 4 THEN '转交落地配'
        WHEN l.order_type = 5 THEN '整车'
        ELSE '其他'
    END AS orderType,
CASE
        WHEN DO.type = 0 THEN '提货订单'
        WHEN DO.type = 1 THEN '送货订单'
				WHEN DO.type = 2 THEN '配载到站订单'
				WHEN DO.type = 3 THEN '配载直送订单'
			  WHEN DO.type = 4 THEN '配载提货订单'
				WHEN DO.type = 5 THEN '配载派送'
				WHEN DO.type = 6 THEN '落地配'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='0' THEN '拼车'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='1' THEN '整车'
				WHEN DO.type = 7 THEN '直提直送'
        ELSE '其他'
    END AS 司机单类型,
	if(w.offer_type = '6','是', '否') 是否网货单,
	CASE
        WHEN DO.arrive_pay_type = 0 THEN '到付-货主付'
        WHEN DO.arrive_pay_type = 1 THEN '到付-收货人付'
				WHEN  w.is_collect_pay = 1 THEN '到付'
				WHEN w.payment = 0 THEN '线下支付'
				WHEN w.payment = 1 THEN '线上支付'
				WHEN w.payment = 2 THEN '到付'
        ELSE '其他'
    END AS 支付类型,
	-- 附带的 金额
	round((DO.freight_charge/100.0),2) as 司机报价,
	if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 实际给司机价,
	 (SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
    and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code)) ) as 平台总支出,
 
		(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=DO.order_code and d.receivables_id=DO.accepting_id ) as 司机支出,
		(SELECT
	GROUP_CONCAT( id,'/',update_time SEPARATOR ', ' )
FROM
	pay_info m 
WHERE
	SUBSTRING_INDEX( m.order_no, '_', 1 )= SUBSTRING_INDEX( l.pay_code, '_', 1 ) 
	and pay_status=1 group by SUBSTRING_INDEX( m.order_no, '_', 1 )) 支付码
FROM
	d_logistics_order l
	LEFT JOIN d_waybill w ON l.waybill_id = w.id
	 LEFT JOIN d_vehicle_stowage_list a  ON a.logistic_order_id = l.id  -- 
	LEFT JOIN d_vehicle_stowage b  ON b.id = a.vehicle_stowage_id
	right JOIN d_driver_order  DO ON (DO.logistics_order_id = l.id or DO.id = b.driver_order_id) and DO.status='1'
	LEFT JOIN d_consignor_user u ON u.id = w.create_id
	LEFT JOIN d_cooperation_user u2 ON u.invite_code = u2.invite_code
	LEFT JOIN d_user_housekeeper_ref hr ON hr.user_id = u.id
	LEFT JOIN d_logistics_housekeeper h ON hr.housekeeper_id = h.id
	LEFT JOIN (
    SELECT
    lcg.order_id,
    GROUP_CONCAT( cg.cargo_name SEPARATOR '/' ) AS cargo_name,
    GROUP_CONCAT( cg.weight SEPARATOR '/' ) AS weight,
    GROUP_CONCAT( cg.volume SEPARATOR '/' ) AS volume 
    FROM
    d_logistics_order_cargo lcg
    JOIN d_waybill_cargo cg ON cg.id = lcg.waybill_cargo_id 
    GROUP BY
    lcg.order_id 
  ) cargo ON cargo.order_id = l.id
	LEFT JOIN d_logistics_user u3 ON u3.id = l.accepting_id 
WHERE
	1 = 1 
	and w.waybill_status!=9
	-- and l.status='1'
	-- and DO.status='1'
	and DATE_FORMAT(w.create_time,'%Y-%m')='@a'
	-- and w.create_time>='2024-12-01 00:00:00'
  -- and w.code='YD2508260949590007'
	
UNION 

SELECT
  l.id,
	w.create_time YD创建时间,
	l.create_time WL单创建时间,
  DO.create_time SJ单创建时间,
	CONCAT( COALESCE(u.real_name,''), '/',COALESCE(u.phone,''),'/', COALESCE(u.org_name,'')) 客户名称,
	-- w.id waybillId,
	w.CODE waybillCode,
-- 	w.starting_province as starting_province,
-- 	w.starting_city as starting_city,
-- 	w.starting_district as starting_district,
-- 	w.starting_street as starting_street,
-- 	w.consignor_adress as consignor_adress,
-- 	w.destination_province as destination_province,
-- 	w.destination_city as destination_city,
-- 	w.destination_district as destination_district,
-- 	w.destination_street as destination_street,
-- 	w.consignee_adress as consignee_adress,
    CONCAT_WS('',w.starting_province,w.starting_city,w.starting_district,w.starting_street,w.consignor_adress) startingAddress,
    CONCAT_WS('',w.destination_province,w.destination_city,w.destination_district,w.destination_street,w.consignee_adress) destinationAddress,
  l.cargo_name 货物名称,
	DO.cargo_pack 货物包装,
	cargo.weight/1000 cargoWeight,
	cargo.volume/1000 cargoVolume,
	round((w.freight_charge/100.0),2) as 货主报价,
	(SELECT
   round(sum(o.amount/100.0),2) as amount
   FROM
   d_order_info o
   WHERE
   ( user_id = '1' OR receivables_id = '1' ) 
   AND STATUS IN ( '2', '4' ) 
   AND order_type != '1' 
   AND order_type != '2' 
   and o.receivables_id = 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))) as 平台总收入,
	
	if(l.accepting_id = 'admin','乐享智运', u3.org_name) 品牌物流,
	-- u3.id,
	l.order_code orderCode,
	
	(SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))
	 and o.receivables_id=u3.id) 线路价,
	 
	DO.driver_name driverName ,
	DO.driver_phone driver_phone,
	DO.license_code licenseCode,
	DO.order_code driverOrder,
  concat_ws('',DO.starting_province,DO.starting_city,DO.starting_district,NULLIF(DO.starting_street,''),NULLIF(DO.starting_adress,'')) 司机单发货地,
	concat_ws('',DO.destination_province,DO.destination_city,DO.destination_district,NULLIF(DO.destination_street,''),NULLIF(DO.destination_adress,'')) 司机单卸货地,
	DO.actual_load_time 装货时间,
-- 	a.unloading_time as 卸货时间1,
--   DO.unload_time 卸货时间,
	COALESCE(a.unloading_time, IF(b.recipient_status IN ('1', '2'), b.update_time, NULL), DO.unload_time) as 卸货时间,
		if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 司机运费,
    CONCAT(if(u2.bind_invite_code='' or u2.bind_invite_code is null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),(select CONCAT( COALESCE(real_name,''), '/', COALESCE(phone,'')) from d_cooperation_user where invite_code=u2.bind_invite_code)),'/',(	if(u2.bind_invite_code='' or u2.bind_invite_code is null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),(select GROUP_CONCAT(CONCAT(profit_ratio, '/', service_ratio) SEPARATOR ', ') from d_invite_code_log t where t.invite_code=u2.bind_invite_code and t.consignor_id=u2.id)))) 一级合伙人,
	CONCAT(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),''),'/',(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),''))) 二级合伙人,
	(select GROUP_CONCAT(round(d.amount/100.0,2) SEPARATOR '/') from d_order_info d where d.order_code=l.order_code /*and d.receivables_id=u2.id*/and d.receivables_id!='null' and d.receivables_user_type='5') as 合伙人佣金支出,
	 CASE
        WHEN w.waybill_status = 0 THEN '待接单'
        WHEN w.waybill_status = 1 THEN '待提货'
        WHEN w.waybill_status = 2 THEN '在途'
        WHEN w.waybill_status = 3 THEN '待确认'
        WHEN w.waybill_status = 4 THEN '已完成'
        WHEN w.waybill_status = 5 THEN '待结算'
        WHEN w.waybill_status = 6 THEN '已取消'
        WHEN w.waybill_status = 7 THEN '复磅中'
        WHEN w.waybill_status = 8 THEN '复磅支付'
        WHEN w.waybill_status = 9 THEN '作废'
        ELSE '其他'
    END AS waybillStatus,
		CASE
        WHEN l.del_status = 0 THEN '待收货'
        WHEN l.del_status = 1 THEN '待处理（提货完成）'
        WHEN l.del_status = 2 THEN '转交中'
        WHEN l.del_status = 3 THEN '已转交'
        WHEN l.del_status = 4 THEN '发货中（发布司机订单）'
        WHEN l.del_status = 5 THEN '已发货（司机装货）'
        WHEN l.del_status = 6 THEN '配载中'
        WHEN l.del_status = 7 THEN '配载完成'
        WHEN l.del_status = 8 THEN '已完成'
        WHEN l.del_status = 9 THEN '待结算'
				WHEN l.del_status = 10 THEN '待复磅'
				WHEN l.del_status = 11 THEN '已复磅'
				WHEN l.del_status = 12 THEN '作废'
				WHEN l.del_status = 13 THEN '复磅支付'
				WHEN l.del_status = 14 THEN '处理中'
				WHEN l.del_status = 15 THEN '到货待确认'
				WHEN l.del_status = 16 THEN '复磅待确认'
				WHEN l.del_status = 17 THEN '待付服务费'
        ELSE '其他'
    END AS logisticStatus,
			 CASE
        WHEN l.order_type = 0 THEN '零担'
        WHEN l.order_type = 1 THEN '合作物流'
        WHEN l.order_type = 2 THEN '配载'
        WHEN l.order_type = 3 THEN '转交'
        WHEN l.order_type = 4 THEN '转交落地配'
        WHEN l.order_type = 5 THEN '整车'
        ELSE '其他'
    END AS orderType,
	CASE
        WHEN DO.type = 0 THEN '提货订单'
        WHEN DO.type = 1 THEN '送货订单'
				WHEN DO.type = 2 THEN '配载到站订单'
				WHEN DO.type = 3 THEN '配载直送订单'
			  WHEN DO.type = 4 THEN '配载提货订单'
				WHEN DO.type = 5 THEN '配载派送'
				WHEN DO.type = 6 THEN '落地配'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='0' THEN '拼车'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='1' THEN '整车'
				WHEN DO.type = 7 THEN '直提直送'
        ELSE '其他'
    END AS 司机单类型,
    if(w.offer_type = '6','是', '否') 是否网货单,
	CASE
        WHEN DO.arrive_pay_type = 0 THEN '到付-货主付'
        WHEN DO.arrive_pay_type = 1 THEN '到付-收货人付'
				WHEN  w.is_collect_pay = 1 THEN '到付'
				WHEN w.payment = 0 THEN '线下支付'
				WHEN w.payment = 1 THEN '线上支付'
				WHEN w.payment = 2 THEN '到付'
        ELSE '其他'
    END AS 支付类型,
	-- 附带的 金额
	round((DO.freight_charge/100.0),2) as 司机报价,
	if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 实际给司机价,
	 (SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
    and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code)) ) as 平台总支出,
 
		(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=DO.order_code and d.receivables_id=DO.accepting_id ) as 司机支出,
			(SELECT
	GROUP_CONCAT( id,'/',update_time SEPARATOR ', ' )
FROM
	pay_info m 
WHERE
	SUBSTRING_INDEX( m.order_no, '_', 1 )= SUBSTRING_INDEX( l.pay_code, '_', 1 ) 
	and pay_status=1 group by SUBSTRING_INDEX( m.order_no, '_', 1 )) 支付码
FROM
	d_logistics_order l
	LEFT JOIN d_waybill w ON l.waybill_id = w.id
  LEFT JOIN d_vehicle_stowage_list a  ON a.logistic_order_id = l.id  -- 
	LEFT JOIN d_vehicle_stowage b  ON b.id = a.vehicle_stowage_id
	LEFT JOIN d_driver_order  DO ON (DO.logistics_order_id = l.id or DO.id = b.driver_order_id) and DO.status='1'
	LEFT JOIN d_consignor_user u ON u.id = w.create_id
	LEFT JOIN d_cooperation_user u2 ON u.invite_code = u2.invite_code
	LEFT JOIN d_user_housekeeper_ref hr ON hr.user_id = u.id
	LEFT JOIN d_logistics_housekeeper h ON hr.housekeeper_id = h.id
	LEFT JOIN (
    SELECT
    lcg.order_id,
    GROUP_CONCAT( cg.cargo_name SEPARATOR '/' ) AS cargo_name,
    GROUP_CONCAT( cg.weight SEPARATOR '/' ) AS weight,
    GROUP_CONCAT( cg.volume SEPARATOR '/' ) AS volume 
    FROM
    d_logistics_order_cargo lcg
    JOIN d_waybill_cargo cg ON cg.id = lcg.waybill_cargo_id 
    GROUP BY
    lcg.order_id 
  ) cargo ON cargo.order_id = l.id
	LEFT JOIN d_logistics_user u3 ON u3.id = l.accepting_id 
WHERE
	1 = 1 
	and w.waybill_status!=9
	-- and l.status='1'
	-- and DO.status='1'
	-- and b.recipient_status in ('1','2')
  and DATE_FORMAT(w.create_time,'%Y-%m')='@a'
	-- and w.create_time>='2024-12-01 00:00:00'
  -- and w.code in ('YD2508260949590007')
  ) ww
	ORDER BY
	ww.YD创建时间 desc, ww.WL单创建时间,ww.SJ单创建时间 asc;
        """

#     query_waybill_list1="""
#     SELECT
# 	DISTINCT
#   l.id,
# 	w.create_time YD创建时间,
# 	l.create_time WL单创建时间,
#   DO.create_time SJ单创建时间,
# 	CONCAT( COALESCE(u.real_name,''), '/',COALESCE(u.phone,''),'/', COALESCE(u.org_name,'')) 客户名称,
# 	-- w.id waybillId,
# 	w.CODE waybillCode,
#     CONCAT_WS('',w.starting_province,w.starting_city,w.starting_district,w.starting_street,w.consignor_adress) startingAddress,
#     CONCAT_WS('',w.destination_province,w.destination_city,w.destination_district,w.destination_street,w.consignee_adress) destinationAddress,
#   l.cargo_name 货物名称,
# 	DO.cargo_pack 货物包装,
# 	cargo.weight/1000 cargoWeight,
# 	cargo.volume/1000 cargoVolume,
# 	round((w.freight_charge/100.0),2) as 货主报价,
# 	(SELECT
#    round(sum(o.amount/100.0),2) as amount
#    FROM
#    d_order_info o
#    WHERE
#    ( user_id = '1' OR receivables_id = '1' )
#    AND STATUS IN ( '2', '4' )
#    AND order_type != '1'
#    AND order_type != '2'
#    and o.receivables_id = 1
#    and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))) as 平台总收入,
#
# 	if(l.accepting_id = 'admin','乐享智运', u3.org_name) 品牌物流,
# 	-- u3.id,
# 	l.order_code orderCode,
#
# 	(SELECT
#     round(sum(o.amount/100.0),2) as amount
#     FROM
#     d_order_info o
#     WHERE
#     ( user_id = '1' OR receivables_id = '1' )
#     AND STATUS IN ( '2', '4' )
#     AND order_type != '1'
#     AND order_type != '2'
#     and o.receivables_id != 1
#    and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))
# 	 and o.receivables_id=u3.id) 线路价,
#
# 	DO.driver_name driverName ,
# 	DO.driver_phone driver_phone,
# 	DO.license_code licenseCode,
# 	DO.order_code driverOrder,
#   concat_ws('',DO.starting_province,DO.starting_city,DO.starting_district,NULLIF(DO.starting_street,''),NULLIF(DO.starting_adress,'')) 司机单发货地,
# 	concat_ws('',DO.destination_province,DO.destination_city,DO.destination_district,NULLIF(DO.destination_street,''),NULLIF(DO.destination_adress,'')) 司机单卸货地,
# 	DO.actual_load_time 装货时间,
# -- 	a.unloading_time as 卸货时间1,
# --   DO.unload_time 卸货时间,
# 	COALESCE(a.unloading_time, IF(b.recipient_status IN ('1', '2'), b.update_time, NULL), DO.unload_time) as 卸货时间,
# 		if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 司机运费,
#     CONCAT(if(u2.bind_invite_code='' or u2.bind_invite_code is null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),(select CONCAT( COALESCE(real_name,''), '/', COALESCE(phone,'')) from d_cooperation_user where invite_code=u2.bind_invite_code)),'/',(	if(u2.bind_invite_code='' or u2.bind_invite_code is null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),(select GROUP_CONCAT(CONCAT(profit_ratio, '/', service_ratio) SEPARATOR ', ') from d_invite_code_log t where t.invite_code=u2.bind_invite_code and t.consignor_id=u2.id)))) 一级合伙人,
# 	CONCAT(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),''),'/',(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),''))) 二级合伙人,
# 	(select GROUP_CONCAT(round(d.amount/100.0,2) SEPARATOR '/') from d_order_info d where d.order_code=l.order_code /*and d.receivables_id=u2.id*/and d.receivables_id!='null' and d.receivables_user_type='5') as 合伙人佣金支出,
# 	 CASE
#         WHEN w.waybill_status = 0 THEN '待接单'
#         WHEN w.waybill_status = 1 THEN '待提货'
#         WHEN w.waybill_status = 2 THEN '在途'
#         WHEN w.waybill_status = 3 THEN '待确认'
#         WHEN w.waybill_status = 4 THEN '已完成'
#         WHEN w.waybill_status = 5 THEN '待结算'
#         WHEN w.waybill_status = 6 THEN '已取消'
#         WHEN w.waybill_status = 7 THEN '复磅中'
#         WHEN w.waybill_status = 8 THEN '复磅支付'
#         WHEN w.waybill_status = 9 THEN '作废'
#         ELSE '其他'
#     END AS waybillStatus,
# 		CASE
#         WHEN l.del_status = 0 THEN '待收货'
#         WHEN l.del_status = 1 THEN '待处理（提货完成）'
#         WHEN l.del_status = 2 THEN '转交中'
#         WHEN l.del_status = 3 THEN '已转交'
#         WHEN l.del_status = 4 THEN '发货中（发布司机订单）'
#         WHEN l.del_status = 5 THEN '已发货（司机装货）'
#         WHEN l.del_status = 6 THEN '配载中'
#         WHEN l.del_status = 7 THEN '配载完成'
#         WHEN l.del_status = 8 THEN '已完成'
#         WHEN l.del_status = 9 THEN '待结算'
# 				WHEN l.del_status = 10 THEN '待复磅'
# 				WHEN l.del_status = 11 THEN '已复磅'
# 				WHEN l.del_status = 12 THEN '作废'
# 				WHEN l.del_status = 13 THEN '复磅支付'
# 				WHEN l.del_status = 14 THEN '处理中'
# 				WHEN l.del_status = 15 THEN '到货待确认'
# 				WHEN l.del_status = 16 THEN '复磅待确认'
# 				WHEN l.del_status = 17 THEN '待付服务费'
#         ELSE '其他'
#     END AS logisticStatus,
# 			 CASE
#         WHEN l.order_type = 0 THEN '零担'
#         WHEN l.order_type = 1 THEN '合作物流'
#         WHEN l.order_type = 2 THEN '配载'
#         WHEN l.order_type = 3 THEN '转交'
#         WHEN l.order_type = 4 THEN '转交落地配'
#         WHEN l.order_type = 5 THEN '整车'
#         ELSE '其他'
#     END AS orderType,
# 	CASE
#         WHEN DO.type = 0 THEN '提货订单'
#         WHEN DO.type = 1 THEN '送货订单'
# 				WHEN DO.type = 2 THEN '配载到站订单'
# 				WHEN DO.type = 3 THEN '配载直送订单'
# 			  WHEN DO.type = 4 THEN '配载提货订单'
# 				WHEN DO.type = 5 THEN '配载派送'
# 				WHEN DO.type = 6 THEN '落地配'
# 				WHEN l.accepting_id = 'admin' and DO.transport_mod='0' THEN '拼车'
# 				WHEN l.accepting_id = 'admin' and DO.transport_mod='1' THEN '整车'
# 				WHEN DO.type = 7 THEN '直提直送'
#         ELSE '其他'
#     END AS 司机单类型,
#     if(w.offer_type = '6','是', '否') 是否网货单,
# 	CASE
#         WHEN DO.arrive_pay_type = 0 THEN '到付-货主付'
#         WHEN DO.arrive_pay_type = 1 THEN '到付-收货人付'
# 				WHEN  w.is_collect_pay = 1 THEN '到付'
# 				WHEN w.payment = 0 THEN '线下支付'
# 				WHEN w.payment = 1 THEN '线上支付'
# 				WHEN w.payment = 2 THEN '到付'
#         ELSE '其他'
#     END AS 支付类型,
# 	-- 附带的 金额
# 	round((DO.freight_charge/100.0),2) as 司机报价,
# 	if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 实际给司机价,
# 	 (SELECT
#     round(sum(o.amount/100.0),2) as amount
#     FROM
#     d_order_info o
#     WHERE
#     ( user_id = '1' OR receivables_id = '1' )
#     AND STATUS IN ( '2', '4' )
#     AND order_type != '1'
#     AND order_type != '2'
#     and o.receivables_id != 1
#     and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code)) ) as 平台总支出,
#
# 		(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=DO.order_code and d.receivables_id=DO.accepting_id ) as 司机支出,
# 			(SELECT
# 	GROUP_CONCAT( id,'/',update_time SEPARATOR ', ' )
# FROM
# 	pay_info m
# WHERE
# 	SUBSTRING_INDEX( m.order_no, '_', 1 )= SUBSTRING_INDEX( l.pay_code, '_', 1 )
# 	and pay_status=1 group by SUBSTRING_INDEX( m.order_no, '_', 1 )) 支付码
# FROM
# 	d_logistics_order l
# 	LEFT JOIN d_waybill w ON l.waybill_id = w.id
#   LEFT JOIN d_vehicle_stowage_list a  ON a.logistic_order_id = l.id  --
# 	LEFT JOIN d_vehicle_stowage b  ON b.id = a.vehicle_stowage_id
# 	LEFT JOIN d_driver_order  DO ON (DO.logistics_order_id = l.id or DO.id = b.driver_order_id) and DO.status='1'
# 	LEFT JOIN d_consignor_user u ON u.id = w.create_id
# 	LEFT JOIN d_cooperation_user u2 ON u.invite_code = u2.invite_code
# 	LEFT JOIN d_user_housekeeper_ref hr ON hr.user_id = u.id
# 	LEFT JOIN d_logistics_housekeeper h ON hr.housekeeper_id = h.id
# 	LEFT JOIN (
#     SELECT
#     lcg.order_id,
#     GROUP_CONCAT( cg.cargo_name SEPARATOR '/' ) AS cargo_name,
#     GROUP_CONCAT( cg.weight SEPARATOR '/' ) AS weight,
#     GROUP_CONCAT( cg.volume SEPARATOR '/' ) AS volume
#     FROM
#     d_logistics_order_cargo lcg
#     JOIN d_waybill_cargo cg ON cg.id = lcg.waybill_cargo_id
#     GROUP BY
#     lcg.order_id
#   ) cargo ON cargo.order_id = l.id
# 	LEFT JOIN d_logistics_user u3 ON u3.id = l.accepting_id
# WHERE
# 	1 = 1
# 	and w.waybill_status!=9
# 	-- and l.status='1'
# 	-- and DO.status='1'
# 	-- and b.recipient_status in ('1','2')
#   and DATE_FORMAT(w.create_time,'%Y-%m')='@a'
# 	-- and w.create_time>='2024-12-01 00:00:00'
#   -- and w.code in ('YD2508260949590007')
#   	ORDER BY
# 	w.create_time desc, l.create_time,DO.create_time asc;
#     """

    query_waybill_list1 = """
    SELECT
	DISTINCT
  l.id,
	w.create_time YD创建时间,
	l.create_time WL单创建时间,
  DO.create_time SJ单创建时间,
	CONCAT( COALESCE(u.real_name,''), '/',COALESCE(u.phone,''),'/', COALESCE(u.org_name,''),'/',COALESCE(u.is_monthly_balance,'')) 客户名称,
	-- w.id waybillId,
	w.CODE waybillCode,
    CONCAT_WS('',w.starting_province,w.starting_city,w.starting_district,w.starting_street,w.consignor_adress) startingAddress,
    CONCAT_WS('',w.destination_province,w.destination_city,w.destination_district,w.destination_street,w.consignee_adress) destinationAddress,
  l.cargo_name 货物名称,
	DO.cargo_pack 货物包装,
	cargo.weight/1000 cargoWeight,
	cargo.volume/1000 cargoVolume,
	round((w.freight_charge/100.0),2) as 货主报价,
	(SELECT
  GROUP_CONCAT(
CONCAT(
	CASE
        WHEN o.type = 1 THEN '微信'
        WHEN o.type = 2 THEN '支付宝'
        WHEN o.type = 3 THEN '银行卡'
        WHEN o.type = 4 THEN '余额'
				WHEN o.type = 5 THEN '冻结'
        ELSE '其他'
    END,
		'/',
	round(amount/100,2),
	'/',
CODE) SEPARATOR '|')
   FROM
   d_order_info o
   WHERE
   ( user_id = '1' OR receivables_id = '1' ) 
   AND STATUS IN ( '2', '4' ) 
   AND order_type != '1' 
   AND order_type != '2' 
   and o.receivables_id = 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))) as 平台收入,
	
	if(l.accepting_id = 'admin','乐享智运', u3.org_name) 品牌物流,
	-- u3.id,
	l.order_code orderCode,
	
	(SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))
	 and o.receivables_id=u3.id) 线路价,
	 
	DO.driver_name driverName ,
	DO.driver_phone driver_phone,
	DO.license_code licenseCode,
	DO.order_code driverOrder,
  concat_ws('',DO.starting_province,DO.starting_city,DO.starting_district,NULLIF(DO.starting_street,''),NULLIF(DO.starting_adress,'')) 司机单发货地,
	concat_ws('',DO.destination_province,DO.destination_city,DO.destination_district,NULLIF(DO.destination_street,''),NULLIF(DO.destination_adress,'')) 司机单卸货地,
	DO.actual_load_time 装货时间,
-- 	a.unloading_time as 卸货时间1,
--   DO.unload_time 卸货时间,
	COALESCE(a.unloading_time, IF(b.recipient_status IN ('1', '2'), b.update_time, NULL), DO.unload_time) as 卸货时间,
		if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 司机运费,
    CONCAT(if(u2.bind_invite_code='' or u2.bind_invite_code is null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),(select CONCAT( COALESCE(real_name,''), '/', COALESCE(phone,'')) from d_cooperation_user where invite_code=u2.bind_invite_code)),'/',(	if(u2.bind_invite_code='' or u2.bind_invite_code is null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),(select GROUP_CONCAT(CONCAT(profit_ratio, '/', service_ratio) SEPARATOR ', ') from d_invite_code_log t where t.invite_code=u2.bind_invite_code and t.consignor_id=u2.id)))) 一级合伙人,
	CONCAT(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),''),'/',(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),''))) 二级合伙人,
	(select GROUP_CONCAT(round(d.amount/100.0,2) SEPARATOR '/') from d_order_info d where d.order_code=l.order_code /*and d.receivables_id=u2.id*/and d.receivables_id!='null' and d.receivables_user_type='5') as 合伙人佣金支出,
	 CASE
        WHEN w.waybill_status = 0 THEN '待接单'
        WHEN w.waybill_status = 1 THEN '待提货'
        WHEN w.waybill_status = 2 THEN '在途'
        WHEN w.waybill_status = 3 THEN '待确认'
        WHEN w.waybill_status = 4 THEN '已完成'
        WHEN w.waybill_status = 5 THEN '待结算'
        WHEN w.waybill_status = 6 THEN '已取消'
        WHEN w.waybill_status = 7 THEN '复磅中'
        WHEN w.waybill_status = 8 THEN '复磅支付'
        WHEN w.waybill_status = 9 THEN '作废'
        ELSE '其他'
    END AS waybillStatus,
		CASE
        WHEN l.del_status = 0 THEN '待收货'
        WHEN l.del_status = 1 THEN '待处理（提货完成）'
        WHEN l.del_status = 2 THEN '转交中'
        WHEN l.del_status = 3 THEN '已转交'
        WHEN l.del_status = 4 THEN '发货中（发布司机订单）'
        WHEN l.del_status = 5 THEN '已发货（司机装货）'
        WHEN l.del_status = 6 THEN '配载中'
        WHEN l.del_status = 7 THEN '配载完成'
        WHEN l.del_status = 8 THEN '已完成'
        WHEN l.del_status = 9 THEN '待结算'
				WHEN l.del_status = 10 THEN '待复磅'
				WHEN l.del_status = 11 THEN '已复磅'
				WHEN l.del_status = 12 THEN '作废'
				WHEN l.del_status = 13 THEN '复磅支付'
				WHEN l.del_status = 14 THEN '处理中'
				WHEN l.del_status = 15 THEN '到货待确认'
				WHEN l.del_status = 16 THEN '复磅待确认'
				WHEN l.del_status = 17 THEN '待付服务费'
        ELSE '其他'
    END AS logisticStatus,
			 CASE
        WHEN l.order_type = 0 THEN '零担'
        WHEN l.order_type = 1 THEN '合作物流'
        WHEN l.order_type = 2 THEN '配载'
        WHEN l.order_type = 3 THEN '转交'
        WHEN l.order_type = 4 THEN '转交落地配'
        WHEN l.order_type = 5 THEN '整车'
        ELSE '其他'
    END AS orderType,
	CASE
        WHEN DO.type = 0 THEN '提货订单'
        WHEN DO.type = 1 THEN '送货订单'
				WHEN DO.type = 2 THEN '配载到站订单'
				WHEN DO.type = 3 THEN '配载直送订单'
			  WHEN DO.type = 4 THEN '配载提货订单'
				WHEN DO.type = 5 THEN '配载派送'
				WHEN DO.type = 6 THEN '落地配'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='0' THEN '拼车'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='1' THEN '整车'
				WHEN DO.type = 7 THEN '直提直送'
        ELSE '其他'
    END AS 司机单类型,
    if(w.offer_type = '6','是', '否') 是否网货单,
	CASE
        WHEN DO.arrive_pay_type = 0 THEN '到付-货主付'
        WHEN DO.arrive_pay_type = 1 THEN '到付-收货人付'
				WHEN  w.is_collect_pay = 1 THEN '到付'
				WHEN w.payment = 0 THEN '线下支付'
				WHEN w.payment = 1 THEN '线上支付'
				WHEN w.payment = 2 THEN '到付'
        ELSE '其他'
    END AS 支付类型,
	-- 附带的 金额
	round((DO.freight_charge/100.0),2) as 司机报价,
	if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 实际给司机价,
	 (SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
    and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code)) ) as 平台总支出,
    (select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=DO.order_code and d.receivables_id=DO.accepting_id and order_type!='QKJE') as 司机支出,
	(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=DO.order_code and d.receivables_id=DO.accepting_id and order_type='QKJE') as 司机请款金额,
	(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=l.order_code and d.receivables_id=l.accepting_id and order_type='QKJE') as 物流请款金额,
 
			(SELECT
	GROUP_CONCAT( id,'/',update_time SEPARATOR ', ' )
FROM
	pay_info m 
WHERE
	SUBSTRING_INDEX( m.order_no, '_', 1 )= SUBSTRING_INDEX( l.pay_code, '_', 1 ) 
	and pay_status=1 group by SUBSTRING_INDEX( m.order_no, '_', 1 )) 支付码,
(SELECT
	if(a.waybill_id,'','Yes')
FROM
	invoice_application_waybill a
	LEFT JOIN invoice_application b ON a.invoice_id = b.id 
WHERE
	b.invoice_status IN (
	'2',
	'3') and a.waybill_id=w.id) as 是否开票
FROM
	d_logistics_order l
	LEFT JOIN d_waybill w ON l.waybill_id = w.id
  LEFT JOIN d_vehicle_stowage_list a  ON a.logistic_order_id = l.id  -- 
	LEFT JOIN d_vehicle_stowage b  ON b.id = a.vehicle_stowage_id
	LEFT JOIN d_driver_order  DO ON (DO.logistics_order_id = l.id or DO.id = b.driver_order_id) and DO.status='1'
	LEFT JOIN d_consignor_user u ON u.id = w.create_id
	LEFT JOIN d_cooperation_user u2 ON u.invite_code = u2.invite_code
	LEFT JOIN d_user_housekeeper_ref hr ON hr.user_id = u.id
	LEFT JOIN d_logistics_housekeeper h ON hr.housekeeper_id = h.id
	LEFT JOIN (
    SELECT
    lcg.order_id,
    GROUP_CONCAT( cg.cargo_name SEPARATOR '/' ) AS cargo_name,
    GROUP_CONCAT( cg.weight SEPARATOR '/' ) AS weight,
    GROUP_CONCAT( cg.volume SEPARATOR '/' ) AS volume 
    FROM
    d_logistics_order_cargo lcg
    JOIN d_waybill_cargo cg ON cg.id = lcg.waybill_cargo_id 
    GROUP BY
    lcg.order_id 
  ) cargo ON cargo.order_id = l.id
	LEFT JOIN d_logistics_user u3 ON u3.id = l.accepting_id 
WHERE
	1 = 1 
	and w.waybill_status!=9
	-- and l.status='1'
	-- and DO.status='1'
	-- and b.recipient_status in ('1','2')
  and DATE_FORMAT(w.create_time,'%Y-%m-%d')='@a'
	-- and code='YD2509160936380007'
	-- and w.create_time>='2024-12-01 00:00:00'
   -- and w.code in ('YD2509302120390007','YD2510100948480005')
  	ORDER BY
	w.create_time desc, l.create_time,DO.create_time asc;
    """

    query_waybill_list_xiaoshou = """
    
	
	SELECT
    distinct
  	w.create_time YD创建时间,
		l.create_time WL单创建时间,
  	DO.create_time SJ单创建时间,
	CONCAT( COALESCE(u.real_name,''), '/',COALESCE(u.phone,''),'/', COALESCE(u.org_name,'')) 客户名称,
	w.CODE waybillCode,
	l.order_code orderCode,
	DO.driver_name driverName ,
	DO.driver_phone driver_phone,
	DO.license_code licenseCode,
	DO.order_code driverOrder,
	DO.actual_load_time 装货时间,
  -- DO.unload_time 卸货时间,
	COALESCE(a.unloading_time, IF(b.recipient_status IN ('1', '2'), b.update_time, NULL), DO.unload_time) as 卸货时间,
		if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 司机运费,
	 CASE
        WHEN w.waybill_status = 0 THEN '待接单'
        WHEN w.waybill_status = 1 THEN '待提货'
        WHEN w.waybill_status = 2 THEN '在途'
        WHEN w.waybill_status = 3 THEN '待确认'
        WHEN w.waybill_status = 4 THEN '已完成'
        WHEN w.waybill_status = 5 THEN '待结算'
        WHEN w.waybill_status = 6 THEN '已取消'
        WHEN w.waybill_status = 7 THEN '复磅中'
        WHEN w.waybill_status = 8 THEN '复磅支付'
        WHEN w.waybill_status = 9 THEN '作废'
        ELSE '其他'
    END AS waybillStatus,
		 CASE
        WHEN l.del_status = 0 THEN '待收货'
        WHEN l.del_status = 1 THEN '待处理（提货完成）'
        WHEN l.del_status = 2 THEN '转交中'
        WHEN l.del_status = 3 THEN '已转交'
        WHEN l.del_status = 4 THEN '发货中（发布司机订单）'
        WHEN l.del_status = 5 THEN '已发货（司机装货）'
        WHEN l.del_status = 6 THEN '配载中'
        WHEN l.del_status = 7 THEN '配载完成'
        WHEN l.del_status = 8 THEN '已完成'
        WHEN l.del_status = 9 THEN '待结算'
				WHEN l.del_status = 10 THEN '待复磅'
				WHEN l.del_status = 11 THEN '已复磅'
				WHEN l.del_status = 12 THEN '作废'
				WHEN l.del_status = 13 THEN '复磅支付'
				WHEN l.del_status = 14 THEN '处理中'
				WHEN l.del_status = 15 THEN '到货待确认'
				WHEN l.del_status = 16 THEN '复磅待确认'
				WHEN l.del_status = 17 THEN '待付服务费'
        ELSE '其他'
    END AS logisticStatus,
			 CASE
        WHEN l.order_type = 0 THEN '零担'
        WHEN l.order_type = 1 THEN '合作物流'
        WHEN l.order_type = 2 THEN '配载'
        WHEN l.order_type = 3 THEN '转交'
        WHEN l.order_type = 4 THEN '转交落地配'
        WHEN l.order_type = 5 THEN '整车'
        ELSE '其他'
    END AS orderType,
CASE
        WHEN DO.type = 0 THEN '提货订单'
        WHEN DO.type = 1 THEN '送货订单'
				WHEN DO.type = 2 THEN '配载到站订单'
				WHEN DO.type = 3 THEN '配载直送订单'
			  WHEN DO.type = 4 THEN '配载提货订单'
				WHEN DO.type = 5 THEN '配载派送'
				WHEN DO.type = 6 THEN '落地配'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='0' THEN '拼车'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='1' THEN '整车'
				WHEN DO.type = 7 THEN '直提直送'
        ELSE '其他'
    END AS 司机单类型,
	if(w.offer_type = '6','是', '否') 是否网货单,
	CASE
        WHEN DO.arrive_pay_type = 0 THEN '到付-货主付'
        WHEN DO.arrive_pay_type = 1 THEN '到付-收货人付'
				WHEN  w.is_collect_pay = 1 THEN '到付'
				WHEN w.payment = 0 THEN '线下支付'
				WHEN w.payment = 1 THEN '线上支付'
				WHEN w.payment = 2 THEN '到付'
        ELSE '其他'
    END AS 支付类型,
		CONCAT(if(u2.bind_invite_code='' or u2.bind_invite_code is null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),(select CONCAT( COALESCE(real_name,''), '/', COALESCE(phone,'')) from d_cooperation_user where invite_code=u2.bind_invite_code)),'/',(	if(u2.bind_invite_code='' or u2.bind_invite_code is null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),(select GROUP_CONCAT(CONCAT(profit_ratio, '/', service_ratio) SEPARATOR ', ') from d_invite_code_log t where t.invite_code=u2.bind_invite_code and t.consignor_id=u2.id)))) 一级合伙人,
	CONCAT(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),''),'/',(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),''))) 二级合伙人
FROM
	d_logistics_order l
	LEFT JOIN d_waybill w ON l.waybill_id = w.id
  LEFT JOIN d_vehicle_stowage_list a  ON a.logistic_order_id = l.id  -- 
	LEFT JOIN d_vehicle_stowage b  ON b.id = a.vehicle_stowage_id
	LEFT JOIN d_driver_order  DO ON (DO.logistics_order_id = l.id or DO.id = b.driver_order_id) and DO.status='1'
	LEFT JOIN d_consignor_user u ON u.id = w.create_id
  LEFT JOIN d_cooperation_user u2 ON u.invite_code = u2.invite_code

WHERE
	1 = 1 
	and w.waybill_status!=9
	-- and l.status='1'
	-- and DO.status='1'
	-- and b.recipient_status in ('1','2')
  and DATE_FORMAT(w.create_time,'%Y-%m')='@a'
	-- and w.create_time>='2025-01-01 00:00:00'
  -- and w.code in ('YD2503031145170002')
	ORDER BY
	w.create_time desc, l.create_time,DO.create_time asc;
    """

    consignor_list_all =""" 
     SELECT
	-- c.id AS id,
	-- c.user_id AS userId,
	u.id as id,
	c.real_name AS realName,
	c.id_card AS idCard,
	-- c.img_card_front AS imgCardFront,
	-- c.img_card_inverse AS imgCardInverse,
	-- c.img_card_hand AS imgCardHand,
	-- c.STATUS AS STATUS,
	-- c.update_user AS updateUser,
	-- c.update_res AS updateRes,
	c.create_time AS 实名时间,
	-- c.update_time AS updateTime,
	a.update_time as 企业认证时间,
	u.user_name AS userName,
	a.company_name AS companyName,
	a.institution_code AS institutionCode,
	b.real_name as 绑定合伙人姓名,
	b.user_name as 绑定合伙人电话,
	b.invite_code as invite_code,
	m.create_time as 绑定时间
FROM
	d_consignor_user_manage c
	LEFT JOIN d_consignor_user u ON c.user_id = u.id 
	LEFT JOIN d_consignor_org_manage a ON c.user_id=a.user_id
	LEFT JOIN d_cooperation_user b on b.invite_code=u.invite_code
	LEFT JOIN d_invite_code_log m on m.invite_code=b.invite_code and m.consignor_id=u.id
WHERE
	1 = 1 
	and c.status='2'
	-- and a.company_name in ('滁州众石玻璃科技有限公司','滁州开发区益展装饰材料加工厂')
	-- and a.status='2'
ORDER BY
	c.create_time DESC;
    """

    waybill_list_all = """
    SELECT
-- 	ns1.id,
-- 	ns1.CODE,
	ns1.`name`,
-- 	ns1.logistics_id AS logisticsId,
-- 	ns1.LEVEL,
-- 	ns1.province,
-- 	ns1.city,
-- 	ns1.district,
-- 	ns1.street,
-- 	ns1.address,
	ns1.contact_person AS contactPerson,
	ns1.contact_phone AS contactPhone,
-- 	ns1.create_user AS `createUser`,
-- 	ns1.update_user AS updateUser,
-- 	ns1.parent_id AS parentId,
-- 	ns1.deposit / 100 AS deposit,
-- 	ns1.parent_project AS parentProject,
	ns1.logo_name AS logoName,
-- 	ns1.logo_img AS logoImg,
-- 	ns1.brand_level AS brandLevel,
-- 	ns2.NAME AS parentName,
	lu.org_code AS orgCode,
-- 	ef.approve_status AS approveStatus,
-- 	ef.remark,
	ns1.update_time updateTime
 -- lu.org_code orgCode 
FROM
	d_logistics_network_site ns1
	LEFT JOIN d_logistics_network_site ns2 ON ns1.parent_id = ns2.id
	LEFT JOIN d_logistics_user lu ON lu.id = ns1.logistics_id
	LEFT JOIN d_electronic_fence ef ON ef.network_site_id = ns1.id 
WHERE
	1 = 1 
	and ns1.LEVEL='P'
ORDER BY
	ns1.update_time desc
    """

    driver_list_all = """
      SELECT
	d.name,
	d.phone,
	i.license_code,
	i.vehicle_length,
	i.capacity,
	i.license_type,
	i.create_time
FROM
	d_car_manage_info i
	JOIN d_driver d ON i.user_id = d.id 
WHERE
	 i.`status`=2
ORDER BY
	i.create_time DESC
    """

    ## 查询用
    waybill_search = """
    SELECT
	DISTINCT
	w.create_time YD创建时间,
	l.create_time WL单创建时间,
  DO.create_time SJ单创建时间,
	CONCAT( COALESCE(u.real_name,''), '/',COALESCE(u.phone,''),'/', COALESCE(u.org_name,''),'/',COALESCE(u.is_monthly_balance,'')) 客户名称,
	-- w.id waybillId,
	w.CODE waybillCode,
    CONCAT_WS('',w.starting_province,w.starting_city,w.starting_district,w.starting_street,w.consignor_adress) startingAddress,
    CONCAT_WS('',w.destination_province,w.destination_city,w.destination_district,w.destination_street,w.consignee_adress) destinationAddress,
  l.cargo_name 货物名称,
	DO.cargo_pack 货物包装,
	cargo.weight/1000 cargoWeight,
	cargo.volume/1000 cargoVolume,
	round((w.freight_charge/100.0),2) as 货主报价,
	(SELECT
  GROUP_CONCAT(
CONCAT(
	CASE
        WHEN o.type = 1 THEN '微信'
        WHEN o.type = 2 THEN '支付宝'
        WHEN o.type = 3 THEN '银行卡'
        WHEN o.type = 4 THEN '余额'
				WHEN o.type = 5 THEN '冻结'
        ELSE '其他'
    END,
		'/',
	round(amount/100,2),
	'/',
CODE) SEPARATOR '|')
   FROM
   d_order_info o
   WHERE
   ( user_id = '1' OR receivables_id = '1' ) 
   AND STATUS IN ( '2', '4' ) 
   AND order_type != '1' 
   AND order_type != '2' 
   and o.receivables_id = 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))) as 平台收入,
	
	if(l.accepting_id = 'admin','乐享智运', u3.org_name) 品牌物流,
	-- u3.id,
	l.order_code orderCode,
	
	(SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
   and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code))
	 and o.receivables_id=u3.id) 线路价,
	 
	DO.driver_name driverName ,
	DO.driver_phone driver_phone,
	DO.license_code licenseCode,
	DO.order_code driverOrder,
  concat_ws('',DO.starting_province,DO.starting_city,DO.starting_district,NULLIF(DO.starting_street,''),NULLIF(DO.starting_adress,'')) 司机单发货地,
	concat_ws('',DO.destination_province,DO.destination_city,DO.destination_district,NULLIF(DO.destination_street,''),NULLIF(DO.destination_adress,'')) 司机单卸货地,
	DO.actual_load_time 装货时间,
-- 	a.unloading_time as 卸货时间1,
--   DO.unload_time 卸货时间,
	COALESCE(a.unloading_time, IF(b.recipient_status IN ('1', '2'), b.update_time, NULL), DO.unload_time) as 卸货时间,
		if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 司机运费,
    CONCAT(if(u2.bind_invite_code='' or u2.bind_invite_code is null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),(select CONCAT( COALESCE(real_name,''), '/', COALESCE(phone,'')) from d_cooperation_user where invite_code=u2.bind_invite_code)),'/',(	if(u2.bind_invite_code='' or u2.bind_invite_code is null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),(select GROUP_CONCAT(CONCAT(profit_ratio, '/', service_ratio) SEPARATOR ', ') from d_invite_code_log t where t.invite_code=u2.bind_invite_code and t.consignor_id=u2.id)))) 一级合伙人,
	CONCAT(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,CONCAT( COALESCE(u2.real_name,''), '/', COALESCE(u2.phone,'')),''),'/',(if(u2.bind_invite_code!='' and u2.bind_invite_code is not null,(select concat(GROUP_CONCAT(profit_ratio SEPARATOR ', '),'/',service_ratio) from d_invite_code_log t where t.invite_code=u.invite_code and t.consignor_id=u.id),''))) 二级合伙人,
	(select GROUP_CONCAT(round(d.amount/100.0,2) SEPARATOR '/') from d_order_info d where d.order_code=l.order_code /*and d.receivables_id=u2.id*/and d.receivables_id!='null' and d.receivables_user_type='5') as 合伙人佣金支出,
	 CASE
        WHEN w.waybill_status = 0 THEN '待接单'
        WHEN w.waybill_status = 1 THEN '待提货'
        WHEN w.waybill_status = 2 THEN '在途'
        WHEN w.waybill_status = 3 THEN '待确认'
        WHEN w.waybill_status = 4 THEN '已完成'
        WHEN w.waybill_status = 5 THEN '待结算'
        WHEN w.waybill_status = 6 THEN '已取消'
        WHEN w.waybill_status = 7 THEN '复磅中'
        WHEN w.waybill_status = 8 THEN '复磅支付'
        WHEN w.waybill_status = 9 THEN '作废'
        ELSE '其他'
    END AS waybillStatus,
		CASE
        WHEN l.del_status = 0 THEN '待收货'
        WHEN l.del_status = 1 THEN '待处理（提货完成）'
        WHEN l.del_status = 2 THEN '转交中'
        WHEN l.del_status = 3 THEN '已转交'
        WHEN l.del_status = 4 THEN '发货中（发布司机订单）'
        WHEN l.del_status = 5 THEN '已发货（司机装货）'
        WHEN l.del_status = 6 THEN '配载中'
        WHEN l.del_status = 7 THEN '配载完成'
        WHEN l.del_status = 8 THEN '已完成'
        WHEN l.del_status = 9 THEN '待结算'
				WHEN l.del_status = 10 THEN '待复磅'
				WHEN l.del_status = 11 THEN '已复磅'
				WHEN l.del_status = 12 THEN '作废'
				WHEN l.del_status = 13 THEN '复磅支付'
				WHEN l.del_status = 14 THEN '处理中'
				WHEN l.del_status = 15 THEN '到货待确认'
				WHEN l.del_status = 16 THEN '复磅待确认'
				WHEN l.del_status = 17 THEN '待付服务费'
        ELSE '其他'
    END AS logisticStatus,
			 CASE
        WHEN l.order_type = 0 THEN '零担'
        WHEN l.order_type = 1 THEN '合作物流'
        WHEN l.order_type = 2 THEN '配载'
        WHEN l.order_type = 3 THEN '转交'
        WHEN l.order_type = 4 THEN '转交落地配'
        WHEN l.order_type = 5 THEN '整车'
        ELSE '其他'
    END AS orderType,
	CASE
        WHEN DO.type = 0 THEN '提货订单'
        WHEN DO.type = 1 THEN '送货订单'
				WHEN DO.type = 2 THEN '配载到站订单'
				WHEN DO.type = 3 THEN '配载直送订单'
			  WHEN DO.type = 4 THEN '配载提货订单'
				WHEN DO.type = 5 THEN '配载派送'
				WHEN DO.type = 6 THEN '落地配'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='0' THEN '拼车'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='1' THEN '整车'
				WHEN DO.type = 7 THEN '直提直送'
        ELSE '其他'
    END AS 司机单类型,
    if(w.offer_type = '6','是', '否') 是否网货单,
	CASE
        WHEN DO.arrive_pay_type = 0 THEN '到付-货主付'
        WHEN DO.arrive_pay_type = 1 THEN '到付-收货人付'
				WHEN  w.is_collect_pay = 1 THEN '到付'
				WHEN w.payment = 0 THEN '线下支付'
				WHEN w.payment = 1 THEN '线上支付'
				WHEN w.payment = 2 THEN '到付'
        ELSE '其他'
    END AS 支付类型,
	-- 附带的 金额
	round((DO.freight_charge/100.0),2) as 司机报价,
	if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 实际给司机价,
	 (SELECT
    round(sum(o.amount/100.0),2) as amount
    FROM
    d_order_info o
    WHERE
    ( user_id = '1' OR receivables_id = '1' ) 
    AND STATUS IN ( '2', '4' ) 
    AND order_type != '1' 
    AND order_type != '2' 
    and o.receivables_id != 1
    and (o.waybill_id = l.waybill_id or o.order_code in (l.order_code,DO.order_code)) ) as 平台总支出,
 
		(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=DO.order_code and d.receivables_id=DO.accepting_id and order_type!='QKJE') as 司机支出,
		(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=DO.order_code and d.receivables_id=DO.accepting_id and order_type='QKJE') as 司机请款金额,
		(select round(sum(d.amount/100.0),2) from d_order_info d where d.order_code=l.order_code and d.receivables_id=l.accepting_id and order_type='QKJE') as 物流请款金额,

			(SELECT
	GROUP_CONCAT( id,'/',update_time SEPARATOR ', ' )
FROM
	pay_info m 
WHERE
	SUBSTRING_INDEX( m.order_no, '_', 1 )= SUBSTRING_INDEX( l.pay_code, '_', 1 ) 
	and pay_status=1 group by SUBSTRING_INDEX( m.order_no, '_', 1 )) 支付码,
(SELECT
	if(a.waybill_id,'','Yes')
FROM
	invoice_application_waybill a
	LEFT JOIN invoice_application b ON a.invoice_id = b.id 
WHERE
	b.invoice_status IN (
	'2',
	'3') and a.waybill_id=w.id) as 是否开票
FROM
	d_logistics_order l
	LEFT JOIN d_waybill w ON l.waybill_id = w.id
  LEFT JOIN d_vehicle_stowage_list a  ON a.logistic_order_id = l.id  -- 
	LEFT JOIN d_vehicle_stowage b  ON b.id = a.vehicle_stowage_id
	LEFT JOIN d_driver_order  DO ON (DO.logistics_order_id = l.id or DO.id = b.driver_order_id) and DO.status='1'
	LEFT JOIN d_consignor_user u ON u.id = w.create_id
	LEFT JOIN d_cooperation_user u2 ON u.invite_code = u2.invite_code
	LEFT JOIN d_user_housekeeper_ref hr ON hr.user_id = u.id
	LEFT JOIN d_logistics_housekeeper h ON hr.housekeeper_id = h.id
	LEFT JOIN (
    SELECT
    lcg.order_id,
    GROUP_CONCAT( cg.cargo_name SEPARATOR '/' ) AS cargo_name,
    GROUP_CONCAT( cg.weight SEPARATOR '/' ) AS weight,
    GROUP_CONCAT( cg.volume SEPARATOR '/' ) AS volume 
    FROM
    d_logistics_order_cargo lcg
    JOIN d_waybill_cargo cg ON cg.id = lcg.waybill_cargo_id 
    GROUP BY
    lcg.order_id 
  ) cargo ON cargo.order_id = l.id
	LEFT JOIN d_logistics_user u3 ON u3.id = l.accepting_id 
WHERE
	1 = 1 
	and w.waybill_status!=9
	-- and l.status='1'
	-- and DO.status='1'
	-- and b.recipient_status in ('1','2')
  -- and DATE_FORMAT(w.create_time,'%Y-%m-%d')='@a'
	and w.code in ('@a')
	-- and w.create_time>='2024-12-01 00:00:00'
   -- and w.code in ('YD2509302120390007','YD2510100948480005')
  	ORDER BY
	w.create_time desc, l.create_time,DO.create_time asc;
    """

    ## 根据WL单获取YD
    yd_by_wl = "select waybill_code from d_logistics_order t where t.order_code='@a';"

    ## 根据司机单获取YD
    yd_by_sj = "select code from d_waybill where id = (select waybill_id from d_driver_order where order_code='@a');"

    ## 根据手机好获取YD
    yd_by_phone = """
    SELECT
	CODE 
FROM
	d_waybill 
WHERE
	id IN (
	SELECT
		waybill_id 
	FROM
		d_driver_order 
	WHERE
	driver_phone = '@a' 
	AND create_time > DATE_SUB( CURDATE(), INTERVAL 1 MONTH ));
    """

    ## 查询司机账单
    search_driver_bill = """
     SELECT
	-- o.id id,
	createTime as 创建时间,
	-- payUserId,
	round((amount/100.0),2) as 收支金额,
	type as 收支类型,
	p.NAME as 付款方,
-- 	-- receivablesId,
	r.NAME as 收款方,
	payType as 收支方式,
	orderType as 账单类型,
-- 	-- waybillId,
	w.CODE as 收支订单号,
	o.orderCode as 上下级单号
FROM
	(
	SELECT
		id,
		create_time createTime,
		user_id payUserId,
		receivables_id receivablesId,
		amount,
	IF
		( receivables_id = '@a', '收入', '支出' ) AS type,
	CASE
			type 
			WHEN 1 THEN
			'微信' 
			WHEN 2 THEN
			'支付宝' 
			WHEN 3 THEN
			'银行卡' 
			WHEN 4 THEN
			'余额' 
			WHEN 5 THEN
			'冻结' 
			WHEN 6 THEN
			'线下' 
			WHEN 7 THEN
			'到付' 
			WHEN 8 THEN
			'月结' 
		END AS payType,
	CASE
			order_type 
			WHEN 1 THEN
			'充值' 
			WHEN 2 THEN
			'提现' 
			WHEN 3 THEN
			'订单支付' 
			WHEN 4 THEN
			'订单结款' 
			WHEN 5 THEN
			'订单补款' 
			WHEN 9 THEN
			'优惠券申购' 
			WHEN 31 THEN
			'订单退款' 
		END AS orderType,
		waybill_id wayBillId,
		order_code orderCode 
	FROM
		d_order_info 
	WHERE
		STATUS IN ( '2', '4' ) 
		AND (
			receivables_id = '@a' 
		OR user_id = '@a')) o
	LEFT JOIN d_waybill w ON w.id = o.wayBillId
	LEFT JOIN (
	SELECT
		id,
	NAME 
	FROM
		(
		SELECT
			id,
			CONCAT( '司机/', NAME ) NAME 
		FROM
			d_driver 
		WHERE
			id != 'admin' UNION
		SELECT
			id,
			CONCAT( '货主/', real_name ) NAME 
		FROM
			d_consignor_user 
		WHERE
			id != 'admin' UNION
		SELECT
			id,
			CONCAT( '合伙人/', real_name ) NAME 
		FROM
			d_cooperation_user 
		WHERE
			id != 'admin' UNION
		SELECT
			id,
			CONCAT(
				'物流/',
			IF
			( real_name = '管理员', '平台', real_name )) NAME 
		FROM
			d_logistics_user UNION
		SELECT
			'1' id,
			'平台分帐' NAME 
		FROM
		DUAL 
		) u 
	) r ON o.receivablesId = r.id
	LEFT JOIN (
	SELECT
		id,
	NAME 
	FROM
		(
		SELECT
			id,
			CONCAT( '司机/', NAME ) NAME 
		FROM
			d_driver 
		WHERE
			id != 'admin' UNION
		SELECT
			id,
			CONCAT( '货主/', real_name ) NAME 
		FROM
			d_consignor_user 
		WHERE
			id != 'admin' UNION
		SELECT
			id,
			CONCAT( '合伙人/', real_name ) NAME 
		FROM
			d_cooperation_user 
		WHERE
			id != 'admin' UNION
		SELECT
			id,
			CONCAT(
				'物流/',
			IF
			( real_name = '管理员', '平台', real_name )) NAME 
		FROM
			d_logistics_user UNION
		SELECT
			'1' id,
			'平台分帐' NAME 
		FROM
		DUAL 
		) u 
	) p ON o.payUserId = p.id 
ORDER BY
	createTime DESC 
    """


    ###########20250903 修改哈密瓜 ###################
    ######################################################
    sql1 = """
    SELECT
DISTINCT
   l.id,
		w.create_time YD创建时间,
		l.create_time WL单创建时间,
  	DO.create_time SJ单创建时间,
	 CONCAT( COALESCE(u.real_name,''), '/',COALESCE(u.phone,''),'/', COALESCE(u.org_name,'')) 客户名称,
	-- w.id waybillId,
	w.CODE waybillCode,
   CONCAT_WS('',w.starting_province,w.starting_city,w.starting_district,w.starting_street,w.consignor_adress) startingAddress,
   CONCAT_WS('',w.destination_province,w.destination_city,w.destination_district,w.destination_street,w.consignee_adress) destinationAddress,
  l.cargo_name 货物名称,
	DO.cargo_pack 货物包装,
-- 	cargo.weight/1000 cargoWeight,
-- 	cargo.volume/1000 cargoVolume,
	round((w.freight_charge/100.0),2) as 货主报价,
	l.order_code orderCode,
	DO.driver_name driverName ,
	DO.driver_phone driver_phone,
	DO.license_code licenseCode,
	DO.order_code driverOrder,
	DO.actual_load_time 装货时间,
	 CASE
        WHEN w.waybill_status = 0 THEN '待接单'
        WHEN w.waybill_status = 1 THEN '待提货'
        WHEN w.waybill_status = 2 THEN '在途'
        WHEN w.waybill_status = 3 THEN '待确认'
        WHEN w.waybill_status = 4 THEN '已完成'
        WHEN w.waybill_status = 5 THEN '待结算'
        WHEN w.waybill_status = 6 THEN '已取消'
        WHEN w.waybill_status = 7 THEN '复磅中'
        WHEN w.waybill_status = 8 THEN '复磅支付'
        WHEN w.waybill_status = 9 THEN '作废'
        ELSE '其他'
    END AS waybillStatus,
		CASE
        WHEN l.del_status = 0 THEN '待收货'
        WHEN l.del_status = 1 THEN '待处理（提货完成）'
        WHEN l.del_status = 2 THEN '转交中'
        WHEN l.del_status = 3 THEN '已转交'
        WHEN l.del_status = 4 THEN '发货中（发布司机订单）'
        WHEN l.del_status = 5 THEN '已发货（司机装货）'
        WHEN l.del_status = 6 THEN '配载中'
        WHEN l.del_status = 7 THEN '配载完成'
        WHEN l.del_status = 8 THEN '已完成'
        WHEN l.del_status = 9 THEN '待结算'
				WHEN l.del_status = 10 THEN '待复磅'
				WHEN l.del_status = 11 THEN '已复磅'
				WHEN l.del_status = 12 THEN '作废'
				WHEN l.del_status = 13 THEN '复磅支付'
				WHEN l.del_status = 14 THEN '处理中'
				WHEN l.del_status = 15 THEN '到货待确认'
				WHEN l.del_status = 16 THEN '复磅待确认'
				WHEN l.del_status = 17 THEN '待付服务费'
        ELSE '其他'
    END AS logisticStatus,
			 CASE
        WHEN l.order_type = 0 THEN '零担'
        WHEN l.order_type = 1 THEN '合作物流'
        WHEN l.order_type = 2 THEN '配载'
        WHEN l.order_type = 3 THEN '转交'
        WHEN l.order_type = 4 THEN '转交落地配'
        WHEN l.order_type = 5 THEN '整车'
        ELSE '其他'
    END AS orderType,
		CASE
        WHEN DO.type = 0 THEN '提货订单'
        WHEN DO.type = 1 THEN '送货订单'
				WHEN DO.type = 2 THEN '配载到站订单'
				WHEN DO.type = 3 THEN '配载直送订单'
			  WHEN DO.type = 4 THEN '配载提货订单'
				WHEN DO.type = 5 THEN '配载派送'
				WHEN DO.type = 6 THEN '落地配'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='0' THEN '拼车'
				WHEN l.accepting_id = 'admin' and DO.transport_mod='1' THEN '整车'
				WHEN DO.type = 7 THEN '直提直送'
        ELSE '其他'
    END AS 司机单类型,
	if(w.offer_type = '6','是', '否') 是否网货单,
	CASE
        WHEN DO.arrive_pay_type = 0 THEN '到付-货主付'
        WHEN DO.arrive_pay_type = 1 THEN '到付-收货人付'
				WHEN  w.is_collect_pay = 1 THEN '到付'
				WHEN w.payment = 0 THEN '线下支付'
				WHEN w.payment = 1 THEN '线上支付'
				WHEN w.payment = 2 THEN '到付'
        ELSE '其他'
    END AS 支付类型,
	-- 附带的 金额
	round((DO.freight_charge/100.0),2) as 司机报价,
	if(DO.actual_payment_fee!='0',round((DO.actual_payment_fee/100.0),2),round((DO.freight_charge/100.0),2)) as 实际给司机价

FROM
	d_logistics_order l
	LEFT JOIN d_waybill w ON l.waybill_id = w.id
	LEFT JOIN d_vehicle_stowage_list a  ON a.logistic_order_id = l.id  -- 
  LEFT JOIN d_vehicle_stowage b  ON b.id = a.vehicle_stowage_id
	LEFT JOIN d_driver_order  DO ON (DO.logistics_order_id = l.id or DO.id = b.driver_order_id) and DO.status='1'
	LEFT JOIN d_consignor_user u ON u.id = w.create_id

WHERE
	1 = 1 
	and w.waybill_status!=9
	and DATE_FORMAT(w.create_time,'%Y-%m')='@a'
	-- and w.create_time>='2024-12-01 00:00:00'
    and w.code='YD2508051348580014'
	ORDER BY	w.create_time desc, l.create_time,DO.create_time asc;
    """

    # 查询货物重量
    search_cargo = """
  SELECT
	cargo.order_id as order_id,
	cargo.weight / 1000 cargoWeight,
	cargo.volume / 1000 cargoVolume 
FROM
	(
	SELECT
		lcg.order_id AS order_id,
		GROUP_CONCAT( cg.cargo_name SEPARATOR '/' ) AS cargo_name,
		GROUP_CONCAT( cg.weight SEPARATOR '/' ) AS weight,
		GROUP_CONCAT( cg.volume SEPARATOR '/' ) AS volume 
	FROM
		d_logistics_order_cargo lcg
		JOIN d_waybill_cargo cg ON cg.id = lcg.waybill_cargo_id 
		where lcg.order_id is not NULL
	GROUP BY
		lcg.order_id 
	) cargo
    """
